package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import model.Outsider;

public class DBOutsider implements IFOutsider
{
	private Connection con;

	public DBOutsider()
	{
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public int createOutsider(Outsider outsider, int jobID) throws SQLException
	{
		int rc = -1;

		String query = "SELECT outsider_id FROM outsider WHERE outsider_name = ? OR outsider_email = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("createOutsider", query);

		try 
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, outsider.getName());
			ps.setString(2, outsider.getEmail());

			ResultSet results = ps.executeQuery();

			if (!results.next())
			{
				query = "INSERT INTO outsider(outsider_name, outsider_phoneno, outsider_email, outsider_city_id, outsider_active, outsider_job_id) VALUES(?, ?, ?, ?, ?, " + (jobID > 0 ? "?" : "NULL") + ")";

				if (DBConnection.doDebug())
					DBConnection.printDebug("createOutsider", query);

				try
				{
					ps = con.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);

					ps.setString(1, outsider.getName());
					ps.setString(2, outsider.getPhoneNo());
					ps.setString(3, outsider.getEmail());
					ps.setInt(4, outsider.getCity().getCityID());
					ps.setBoolean(5, outsider.isActive());

					if (jobID > 0)
						ps.setInt(6, jobID);

					rc = ps.executeUpdate();
					ResultSet insertedRow = ps.getGeneratedKeys();

					if ((rc > 0) && insertedRow.next())
					{
						rc = insertedRow.getInt(1);
					}
				}
				catch (Exception e)
				{
					if (DBConnection.doDebug())
					{
						System.out.println("[ERROR] Query exception - createOutsider - INSERT: " + e);
						e.printStackTrace();
					}
				}
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - createOutsider - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return rc;
	}

	@Override
	public int updateOutsider(Outsider outsider, int jobID) throws SQLException
	{
		int rc = -1;

		String query = "SELECT outsider_id FROM outsider WHERE (outsider_name = ? OR outsider_email = ?) AND outsider_id != ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("updateOutsider", query);

		try 
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, outsider.getName());
			ps.setString(2, outsider.getEmail());
			ps.setInt(3, outsider.getOutsiderID());

			ResultSet results = ps.executeQuery();

			if (!results.next())
			{
				query = "UPDATE outsider SET outsider_name = ?, outsider_phoneno = ?, outsider_email = ?, outsider_city_id = ?, outsider_active = ?, outsider_job_id = " + (jobID > 0 ? "?" : "NULL") + " WHERE outsider_id = ?";

				if (DBConnection.doDebug())
					DBConnection.printDebug("updateOutsider", query);

				try 
				{
					ps = con.prepareStatement(query);

					ps.setString(1, outsider.getName());
					ps.setString(2, outsider.getPhoneNo());
					ps.setString(3, outsider.getEmail());
					ps.setInt(4, outsider.getCity().getCityID());
					ps.setBoolean(5, outsider.isActive());

					if (jobID > 0)
					{
						ps.setInt(6, jobID);
						ps.setInt(7, outsider.getOutsiderID());
					}
					else
					{
						ps.setInt(6, outsider.getOutsiderID());
					}

					rc = ps.executeUpdate();

				}
				catch (Exception e)
				{
					if (DBConnection.doDebug())
					{
						System.out.println("[ERROR] Query exception - updateOutsider - UPDATE: " + e);
						e.printStackTrace();
					}
				}
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - updateOutsider - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return rc;
	}

	@Override
	public int activateOutsider(int outsiderID) throws SQLException
	{
		int rc = -1;

		String query = "UPDATE outsider SET outsider_active = CASE outsider_active WHEN 0 THEN 1 ELSE 0 END WHERE outsider_id = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("activateOutsider", query);

		try 
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, outsiderID);

			rc = ps.executeUpdate();

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - activateOutsider - UPDATE: " + e);
				e.printStackTrace();
			}
		}

		return rc;
	}
}